package com.six.work.mapper;

import com.six.work.entity.Applicant;
import com.six.work.entity.Company;
import com.six.work.entity.Invitation;
import com.six.work.entity.Swords;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

@Mapper
public interface ApplicantMapper {
   @Select("SELECT * FROM applicant order by applicant_id desc")
   List<Applicant> findAllApplicant();

   @Select("SELECT applicant.*,state.state_id " +
           "FROM it.applicant join it.state " +
           "on applicant.state=state.state " +
           "WHERE applicant.applicant_name like '%${applicant_name}%' " +
           "order by applicant_id;")
   List<Applicant> findAppByAppName(String applicant_name);

   @Select("SELECT applicant.*,state.state_id " +
           "FROM it.applicant join it.state " +
           "on applicant.state=state.state " +
           "WHERE applicant.applicant_name like '%${applicant_name}%' and state.state_id=#{state_id} " +
           "order by applicant_id; ")
   List<Applicant> findAppByStateAndAppName(@Param("applicant_name") String applicant_name, @Param("state_id")Integer state_id);

   @Select("SELECT * FROM it.applicant where job_creatdate is not null order by applicant_id desc")
   List<Applicant> findAllApplicantJob();

   //求职者发布管理的查询
   @Select("SELECT * from applicant where applicant_name like '%${applicant_name}%' and job_creatdate is not null")
   List<Applicant> findAppJobByName(String appName);

@Delete("DELETE from applicant where applicant_id=#{applicant_id}")
   Integer DelAppByID(Integer applicant_id);

   //求职者信息管理查询
@Select("SELECT * from applicant where applicant_name like '%${appName}%' \n" +
        "order by applicant_id desc")
   List<Applicant> findAppByName(String appName);



   @Update("UPDATE applicant SET `job_state` = '已驳回' \n" +
        "WHERE (applicant_id = #{applicant_id})")
   String updateAppState(Integer applicant_id);

   @Update("UPDATE applicant SET `job_state` = '已通过' \n" +
           "WHERE (applicant_id = #{applicant_id})")
   String updateAppState1(Integer applicant_id);


   @Select("select * from applicant where applicant_number=#{applicant_number}")
   Applicant UserLogin(String applicant_number);

   @Insert("insert into applicant (applicant_name,applicant_number,applicant_pass,applicant_tel,applicant_email,applicant_sex) values(#{applicant_name},#{applicant_number},#{applicant_pass},#{applicant_tel},#{applicant_email},#{applicant_sex})")
   Integer insertApp(Applicant applicant);

   @Update("update applicant set applicant_name=#{applicant_name},applicant_img_url=#{applicant_img_url},university=#{university},degree=#{degree},applicant_con=#{applicant_con},job_type=#{job_type},applicant_time=#{applicant_time},applicant_city=#{applicant_city},applicant_site=#{applicant_site},job=#{job},applicant_time1=#{applicant_time1},applicant_time2=#{applicant_time2} where applicant_id=#{applicant_id}")
   Integer updateApplicant(Applicant applicant);

   @Update("update applicant set applicant_pass=#{applicant_pass} where applicant_id=#{applicant_id}")
   Integer updateApp(Applicant applicant);

   @Select("SELECT * from applicant where applicant_id=#{applicant_id}")
   Applicant SelectAppByID(Integer applicant_id);

   //可视化
   @Select("select date_format(applicant_creatdate,'%Y-%m-%d') as create_date,sum(count(*)) over (order by date_format(applicant_creatdate,'%Y-%m-%d')) as companysum from applicant group by date_format(applicant_creatdate,'%Y-%m-%d')")
   List<Applicant> findAllApp();

   //今日新增
   @Select("select count(*) as TodaysumApp from applicant where date(applicant_creatdate) = curdate();;")
   Integer FindTodayCom();

   //本月新增
   @Select("select count(*) as MonthsumApp from applicant where date_format(applicant_creatdate,'%Y-%m')=date_format(now(),'%Y-%m')")
   Integer FindMonthCom();
}
